package com.gxy.learn.jpa.repository;

import com.gxy.learn.jpa.entity.User;
import com.gxy.learn.jpa.vo.UserRoleVO;
import com.gxy.learn.jpa.vo.UserVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * author: Gao xueyong
 * Create at: 2022/2/22 上午12:00
 */
public interface UserRepository extends JpaRepository<User, Long> {

    /**
     * 返回部分字段（必须是完整的类路径）
     *
     * @return
     */
    @Query(value = "select new com.gxy.learn.jpa.vo.UserVO(u.id,u.name,u.mobile) from User u")
    List<UserVO> listRecord();

    /**
     * 返回部分字段（必须是完整的类路径）
     *
     * @return
     */
    @Query(value = "select new com.gxy.learn.jpa.vo.UserVO(u.id,u.name,u.mobile) from User u where u.name like %:name%")
    List<UserVO> listRecord(@Param("name") String name);

    /**
     * 原生sql查询 可以直接返回List<map>
     *
     * @return
     */
    @Query(value = "select u.id,u.name,u.mobile from jpa_user u", nativeQuery = true)
    List<Map<String, Object>> listRecordBySql();

    /**
     * 返回部分字段（必须是完整的类路径）
     *
     * @return
     */
    @Query(value = "select new com.gxy.learn.jpa.vo.UserRoleVO(u.id,u.name,u.mobile,r.name as roleName,r.enname as roleEnName) from User u left join UserRole ur on u.id = ur.userId " +
            "left join Role r on ur.roleId = r.id")
    List<UserRoleVO> listUserRoleRecord();

    /**
     * 分页
     * @param name
     * @param pageable
     * @return
     */
    Page<User> findByName(String name,Pageable pageable);


    /**
     * 原生sql查询  全属性可以返回对象
     *
     * @return
     */
    @Query(value = "select * from jpa_user u", nativeQuery = true)
    List<User> listRecordBySql2();


    /**
     * 通过name模糊查询
     * @param name
     * @return
     */
    List<User> findByNameLike(String name);

    /**
     * 按id排序获取第一个
     * @return
     */
    User findFirstByOrderById();

    /**
     * 按id排倒序取第一个
     * @return
     */
    User findTop1ByOrderByIdDesc();

    /**
     * 按照name和age查询并按id排倒序取第一个
     * @param name
     * @param age
     * @return
     */
    User findFirstByNameAndAgeOrderByIdDesc(String name,Integer age);





    /**
     * 更新
     *
     * @param name
     * @param id
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query(value = "update User u set u.name = :name where u.id = :id")
    int modidyObj(@Param("name") String name, @Param("id") Long id);


    /**
     * 更新
     *
     * @param name
     * @param id
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query(value = "update jpa_user u set u.name =:name where u.id = :id", nativeQuery = true)
    int modidyObjBySql(@Param("name") String name, @Param("id") Long id);


    @Query(value = "select count(1) from (select count(name) from jpa_user group by name) tt", nativeQuery = true)
    int countName();

}
